﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using Microsoft.VisualBasic.CompilerServices;
using System.Runtime.CompilerServices;
using Microsoft.VisualBasic;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.IO;
using FSY.Globals;
using FSY.API.CacheManager;
using FSY.API.Utilities;
namespace FSY.API.DataBase
{
    public sealed class Fsy_SqlHelper
    {
        #region private utility methods & constructors
        private Fsy_SqlHelper()
        {
        }

        private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
        {
            if ((commandParameters != null) || (parameterValues != null))
            {
                if (commandParameters.Length != parameterValues.Length)
                {
                    throw new ArgumentException("Parameter count does not match Parameter Value count.");
                }
                int num2 = commandParameters.Length - 1;
                int num3 = num2;
                for (int i = 0; i <= num3; i++)
                {
                    if (parameterValues[i] is IDbDataParameter)
                    {
                        IDbDataParameter parameter = (IDbDataParameter)parameterValues[i];
                        if (parameter.Value == null)
                        {
                            commandParameters[i].Value = DBNull.Value;
                        }
                        else
                        {
                            commandParameters[i].Value = RuntimeHelpers.GetObjectValue(parameter.Value);
                        }
                    }
                    else if (parameterValues[i] == null)
                    {
                        commandParameters[i].Value = DBNull.Value;
                    }
                    else
                    {
                        commandParameters[i].Value = RuntimeHelpers.GetObjectValue(parameterValues[i]);
                    }
                }
            }
        }

        private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow)
        {
            if ((commandParameters != null) && (dataRow != null))
            {
                foreach (SqlParameter parameter in commandParameters)
                {
                    int num = 0;
                    if ((parameter.ParameterName == null) || (parameter.ParameterName.Length <= 1))
                    {
                        throw new Exception(string.Format("Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: ' {1}' .", num, parameter.ParameterName));
                    }
                    if (dataRow.Table.Columns.IndexOf(parameter.ParameterName.Substring(1)) != -1)
                    {
                        parameter.Value = RuntimeHelpers.GetObjectValue(dataRow[parameter.ParameterName.Substring(1)]);
                    }
                    num++;
                }
            }
        }

        private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            if (command == null)
            {
                throw new ArgumentNullException("command");
            }
            if (commandParameters != null)
            {
                foreach (SqlParameter parameter in commandParameters)
                {
                    if (parameter != null)
                    {
                        if (((parameter.Direction == ParameterDirection.InputOutput) || (parameter.Direction == ParameterDirection.Input)) && (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        command.Parameters.Add(parameter);
                    }
                }
            }
        }

        private static SqlConnection GetSqlConnection(string connectionString)
        {
            SqlConnection conn;
            try
            {
                conn = new SqlConnection(connectionString);
            }
            catch
            {
                throw new Exception("Sql connection string is invalid");
            }
            return conn;
        }

        public static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns)
        {
            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }
            SqlCommand command = new SqlCommand(spName, connection);
            command.CommandType = CommandType.StoredProcedure;
            if ((sourceColumns != null) && (sourceColumns.Length > 0))
            {
                SqlParameter[] spParameterSet = Fsy_SqlHelperParameterCache.GetSpParameterSet(connection, spName);
                int num2 = sourceColumns.Length - 1;
                for (int i = 0; i <= num2; i++)
                {
                    spParameterSet[i].SourceColumn = sourceColumns[i];
                }
                AttachParameters(command, spParameterSet);
            }
            return command;
        }

        private static object ExecuteCommand(string connectionString, CommandType commandType, SqlExecuteType executeType, string commandText, object[] parameterValues, SqlTransaction transaction)
        {
            try
            {
                SqlConnection conn = new SqlConnection(connectionString);
                SqlCommand cmd = new SqlCommand(commandText, conn);
                cmd.CommandType = commandType;
                if (commandType == CommandType.Text)
                {
                    AttachParameters(cmd, (SqlParameter[])parameterValues);
                }
                else
                {
                    SqlParameter[] cacheParameters = Fsy_SqlHelperParameterCache.GetCachedParameterSet(connectionString, commandText);
                    if (cacheParameters == null)
                    {
                        cacheParameters = Fsy_SqlHelperParameterCache.GetSpParameterSet(connectionString, commandText);
                    }
                    AssignParameterValues(cacheParameters, parameterValues);
                    AttachParameters(cmd, cacheParameters);
                }
                conn.Open();
                object objObject = null;
                SqlDataAdapter objDataAdapter = null;
                switch (executeType)
                {
                    case SqlExecuteType.DataSet:
                        if (transaction != null && transaction.Connection != null)
                        {
                            cmd.Transaction = transaction;
                        }
                        DataSet ds = new DataSet();
                        objDataAdapter = new SqlDataAdapter(cmd);
                        objDataAdapter.Fill(ds);
                        objObject = ds;
                        break;
                    case SqlExecuteType.DataTable:
                        if (transaction != null && transaction.Connection != null)
                        {
                            cmd.Transaction = transaction;
                        }
                        DataTable dtObject = new DataTable("resultDataTable");
                        objDataAdapter = new SqlDataAdapter(cmd);
                        objDataAdapter.Fill(dtObject);
                        objObject = dtObject;
                        break;
                    case SqlExecuteType.IDataReader:
                        if (transaction != null && transaction.Connection != null)
                        {
                            cmd.Transaction = transaction;
                        }
                        objObject = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        break;
                    case SqlExecuteType.NonQuery:
                        if (transaction != null && transaction.Connection != null)
                        {
                            cmd.Transaction = transaction;
                        }
                        objObject = cmd.ExecuteNonQuery();
                        break;
                    case SqlExecuteType.Scalar:
                        if (transaction != null && transaction.Connection != null)
                        {
                            cmd.Transaction = transaction;
                        }
                        objObject = cmd.ExecuteScalar();
                        break;
                    case SqlExecuteType.XmlReader:
                        if (transaction != null && transaction.Connection != null)
                        {
                            cmd.Transaction = transaction;
                        }
                        objObject = cmd.ExecuteXmlReader();
                        break;
                }
                if (executeType != SqlExecuteType.IDataReader)
                    conn.Close();
                return objObject;
            }
            catch (SqlException ex)
            {
                FsyLoggerHelper.WriteError("FSY.API.DataBase.Fsy_SqlHelper - ExecuteCommand() ---" + ex.Message);
                return null;
            }
        }
        #endregion

        #region Return DataSet
        public static DataSet FillDataSet(string connectionString, string spName, params object[] parameterValues)
        {
            return (DataSet)ExecuteCommand(connectionString, CommandType.StoredProcedure, SqlExecuteType.DataSet, spName, parameterValues, (SqlTransaction)null);
        }
        public static DataSet FillDataSet(string connectionString, string commandText, params SqlParameter[] parameterValues)
        {
            return (DataSet)ExecuteCommand(connectionString, CommandType.Text, SqlExecuteType.DataSet, commandText, parameterValues, (SqlTransaction)null);
        }
        #endregion

        #region Return DataTable
        public static DataTable FillDataTable(string connectionString, string spName, params object[] parameterValues)
        {
            return (DataTable)ExecuteCommand(connectionString, CommandType.StoredProcedure, SqlExecuteType.DataTable, spName, parameterValues, (SqlTransaction)null);
        }
        public static DataTable FillDataTable(string connectionString, string commandText, params SqlParameter[] parameterValues)
        {
            return (DataTable)ExecuteCommand(connectionString, CommandType.Text, SqlExecuteType.DataTable, commandText, parameterValues, (SqlTransaction)null);
        }
        #endregion

        #region Return DataReader
        public static IDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues)
        {
            return (IDataReader)ExecuteCommand(connectionString, CommandType.StoredProcedure, SqlExecuteType.IDataReader, spName, parameterValues, (SqlTransaction)null);
        }
        public static IDataReader ExecuteReader(string connectionString, string commandText, params SqlParameter[] parameterValues)
        {
            IDataReader dr = (IDataReader)ExecuteCommand(connectionString, CommandType.Text, SqlExecuteType.IDataReader, commandText, parameterValues, (SqlTransaction)null);
            return dr;
        }
        #endregion

        #region NonQuery
        public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
        {
            return (int)ExecuteCommand(connectionString, CommandType.StoredProcedure, SqlExecuteType.NonQuery, spName, parameterValues, (SqlTransaction)null);
        }
        public static int ExecuteNonQuery(string connectionString, string commandText, params SqlParameter[] parameterValues)
        {
            return (int)ExecuteCommand(connectionString, CommandType.Text, SqlExecuteType.NonQuery, commandText, parameterValues, (SqlTransaction)null);
        }
        #endregion

        #region Return Scalar
        public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues)
        {
            return ExecuteCommand(connectionString, CommandType.StoredProcedure, SqlExecuteType.DataSet, spName, parameterValues, (SqlTransaction)null);
        }
        public static object ExecuteScalar(string connectionString, string commandText, params SqlParameter[] parameterValues)
        {
            return ExecuteCommand(connectionString, CommandType.Text, SqlExecuteType.Scalar, commandText, parameterValues, (SqlTransaction)null);
        }
        #endregion

        #region Return XmlReader
        public static XmlReader ExecuteXmlReader(string connectionString, string spName, params object[] parameterValues)
        {
            return (XmlReader)ExecuteCommand(connectionString, CommandType.StoredProcedure, SqlExecuteType.XmlReader, spName, parameterValues, (SqlTransaction)null);
        }
        public static XmlReader ExecuteXmlReader(string connectionString, string commandText, params SqlParameter[] parameterValues)
        {
            return (XmlReader)ExecuteCommand(connectionString, CommandType.Text, SqlExecuteType.XmlReader, commandText, parameterValues, (SqlTransaction)null);
        }
        #endregion

        #region Thuc thi file SQL
        public static int ExecFileSQL(string connectionString, string filePath)
        {
            try
            {
                string s = "";
                if (File.Exists(filePath))
                {
                    FileInfo fi = new FileInfo(filePath);
                    s = fi.OpenText().ReadToEnd();
                }
                return (int)ExecuteCommand(connectionString, CommandType.Text, SqlExecuteType.NonQuery, s, null, (SqlTransaction)null);
            }
            catch
            {
                return -1;
            }
        }
        #endregion

    }
    public sealed class Fsy_SqlHelperParameterCache
    {
        private Fsy_SqlHelperParameterCache()
        {
        }

        public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
        {
            if ((connectionString == null) || (connectionString.Length == 0))
            {
                throw new ArgumentNullException("connectionString");
            }
            if ((commandText == null) || (commandText.Length == 0))
            {
                throw new ArgumentNullException("commandText");
            }
            string str = connectionString + ":" + commandText;
            CacheManager.Fsy_Cache.SetCache(str, commandParameters);
        }

        private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
        {
            int num2 = originalParameters.Length - 1;
            SqlParameter[] parameterArray = new SqlParameter[num2 + 1];
            int num3 = num2;
            for (int i = 0; i <= num3; i++)
            {
                parameterArray[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
            }
            return parameterArray;
        }

        private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter, params object[] parameterValues)
        {
            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }
            if ((spName == null) || (spName.Length == 0))
            {
                throw new ArgumentNullException("spName");
            }
            SqlCommand command = new SqlCommand(spName, connection);
            command.CommandType = CommandType.StoredProcedure;
            connection.Open();
            SqlCommandBuilder.DeriveParameters(command);
            connection.Close();
            if (!includeReturnValueParameter)
            {
                command.Parameters.RemoveAt(0);
            }
            SqlParameter[] parameterArray = new SqlParameter[(command.Parameters.Count - 1) + 1];
            command.Parameters.CopyTo((Array)parameterArray, 0);
            foreach (SqlParameter parameter in parameterArray)
            {
                parameter.Value = DBNull.Value;
            }
            return parameterArray;
        }

        public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
        {
            try
            {
                if ((connectionString == null) || (connectionString.Length == 0))
                {
                    throw new ArgumentNullException("connectionString");
                }
                if ((commandText == null) || (commandText.Length == 0))
                {
                    throw new ArgumentNullException("commandText");
                }
                string str = connectionString + ":" + commandText;
                SqlParameter[] originalParameters = Fsy_Cache.GetCache(str) as SqlParameter[];
                if (originalParameters == null)
                {
                    return null;
                }
                return CloneParameters(originalParameters);
            }
            catch (ArgumentNullException ex)
            {
                FsyLoggerHelper.WriteError("Fsy_SqlHelperParameterCache -- GetCachedParameterSet() " + ex.Message);
            }
            return null;
        }

        public static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
        {
            return GetSpParameterSet(connection, spName, false);
        }

        public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
        {
            return GetSpParameterSet(connectionString, spName, false);
        }

        public static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }
            using (SqlConnection connection2 = (SqlConnection)((ICloneable)connection).Clone())
            {
                return GetSpParameterSetInternal(connection2, spName, includeReturnValueParameter);
            }
        }

        public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
        {
            if ((connectionString == null) || (connectionString.Length == 0))
            {
                throw new ArgumentNullException("connectionString");
            }
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
            }
        }

        private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
        {
            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }
            if ((spName == null) || (spName.Length == 0))
            {
                throw new ArgumentNullException("spName");
            }
            string str = connection.ConnectionString + ":" + spName + Interaction.IIf(includeReturnValueParameter, ":include ReturnValue Parameter", "").ToString();
            SqlParameter[] originalParameters = Fsy_Cache.GetCache(str) as SqlParameter[];
            if (originalParameters == null)
            {
                SqlParameter[] parameterArray3 = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter, new object[0]);
                Fsy_Cache.SetCache(str, parameterArray3);
                originalParameters = parameterArray3;
            }
            return CloneParameters(originalParameters);
        }
    }
}
